Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Be careful not to increase the amount of memory you require beyond that set aside by the operating system. Any paging or swapping caused by over-allocating memory will offset any advantage you get from the library cache.

If you have plenty of memory, you may be able to speed access to the shared SQL areas by setting the Oracle initialization parameter CURSOR_SPACE_FOR_TIME equal to TRUE. When this parameter is TRUE, it specifies that a shared SQL area cannot be deallocated until all the cursors associated with it are closed.

If CURSOR_SPACE_FOR_TIME is TRUE, it is not necessary for Oracle to check whether the SQL statement is in the library cache because this parsed statement cannot be deallocated as long as the cursor is open. If memory is scarce on your system, do not set this parameter. If the value is TRUE and there is no space in the shared pool for a new SQL statement, an error will be returned, thus halting the application.

Data Dictionary Cache

The data dictionary contains a set of tables and views Oracle uses as a reference to the database. Oracle stores information here about both the logical and physical structure of the database. The data dictionary contains information such as the following:

  User information such as user privileges.
  Integrity constraints defined for tables in the database.
  Names and data types of all columns in database tables.
  Information on space allocated and used for schema objects.

The data dictionary is frequently accessed by Oracle itself for the parsing of SQL statements. This access is essential to the operation of Oracle; performance bottlenecks in the data dictionary affect all Oracle users.

You can check the efficiency of the data dictionary cache. Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:

  GETS: The total number of requests for the particular item.
  GETMISSES: The total number of requests resulting in cache misses.

A few number of cache misses are expected, especially during startup when the cache has not been populated. To get an idea of the total number of cache misses, use the following statement:

SQL> SELECT SUM(getmisses) "Cache Misses",
  2  SUM(gets) "Requests",
  3  100 * ( SUM(getmisses) / SUM(gets) ) "Cache Miss Percent"
  4  FROM v$rowcache;

Cache Misses  Requests Cache Miss Percent
------------  -------- ---------- -------
         277      2185          12.677346

The output shown here indicates that a sum of 2,185 requests were made to the data dictionary cache with 277 data dictionary cache misses. This means that 12.68 percent of these requests caused a cache miss.

To get the cache miss statistics on the individual elements of the data dictionary, execute this SQL statement:

SQL> SELECT parameter,
  2  getmisses "Cache Misses",
  3  gets "Requests"
  4  FROM v$rowcache;

PARAMETER                        Cache Misses  Requests
-------------------------------- ------------  --------
dc_free_extents                             4        92
dc_used_extents                             0         0
dc_segments                                 1         7
dc_tablespaces                              0         0
dc_tablespaces                              0         0
dc_tablespace_quotas                        0         0
dc_files                                    0         0
dc_users                                    8        90
dc_rollback_segments                        4       122
dc_objects                                 44       378
dc_constraints                              0         0
dc_object_ids                               0         0
dc_tables                                  20       318
dc_synonyms                                 5         9
dc_sequences                                1         6
dc_usernames                                4        71
dc_database_links                           0         0
dc_histogram_defs                           0         0
dc_profiles                                 0         0
dc_users                                    0         0
dc_columns                                153      1028

PARAMETER                        Cache Misses  Requests
-------------------------------- ------------  --------
dc_table_grants                            14        18
dc_column_grants                            0         0
dc_indexes                                 12       176
dc_constraint_defs                          7         7
dc_constraint_defs                          0         0
dc_sequence_grants                          0         0
dc_user_grants                              7        62

28 rows selected.

In frequently accessed dictionary caches, the miss rate should not rise above 10 to 15 percent. If the percent of misses continues to increase during run time, increase the amount of memory allocated for the data dictionary cache. Use the same parameter as for the library cache: SHARED_POOL_SIZE.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.